package view;

import com.alibaba.fastjson.JSON;
import constant.ConstDataBase;
import model.ViewForExpress;
import model.ViewForGrid;
import pond.common.S;
import pond.common.STRING;
import pond.db.Record;
import pond.db.sql.Criterion;
import pond.db.sql.Sql;
import pond.db.sql.SqlSelect;
import pond.web.Render;
import pond.web.Router;
import service.DataBaseService;

import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * Created by sky_wang on 2017/7/19.
 */
public class View extends Router {
    private DataBaseService dataBaseService;
    public View (DataBaseService dataBaseService){
        this.dataBaseService = dataBaseService;
    }
    {
        get("/list", (req, resp) -> {
            List<ViewForGrid> ret = dataBaseService.getDB().get(t -> t.query(ViewForGrid.class, "select * from view_for_grid;"));
            resp.render(Render.json(ret));
        });
        get("/template", (req, resp) -> {
            String json = req.param("data");
            String sql = dataBaseService.getShowViewReportSql(json);
            if (STRING.isBlank(sql)) {
                resp.send(403, "null sql");
            }

            List<Record> lstRet = dataBaseService.getDB().get(t -> t.query(sql));
            String colStr = dataBaseService.getValueByKeyFromJson("show_columns", json);
            List listCols = JSON.parseArray(colStr);

            List<Map<String, String>> showListRet = new ArrayList<>();
            Date date = new Date();//如果不需要格式,可直接用dt,dt就是当前系统时间
            DateFormat dateFrom = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");//设置显示格式

            S._for(lstRet).each(item -> {
                Map<String, String> map = new LinkedHashMap<String, String>();
                for (Object subItem : listCols) {
                    if (subItem.toString().indexOf("_date") >= 0 || subItem.toString().indexOf("_time") >= 0) {
                        if (null == item.get(subItem.toString()) || 0 == (Long) item.get(subItem.toString())) {
                            map.put(subItem.toString(), "");
                        } else {
                            long dateNo = item.get(subItem.toString());
                            map.put(subItem.toString(), dateFrom.format(new Date(dateNo)));
                        }

                    } else {
                        map.put(subItem.toString(), item.get(subItem.toString()));
                    }
                }
                showListRet.add(map);
            });
            lstRet = lstRet;
            resp.render(Render.json(showListRet));
        });

        get("/:name", (req, resp) -> {
            String view_name = req.paramNonBlank("name", "名称不能为空");
            List<ViewForGrid> ret =  dataBaseService.getDB().get(t -> t.query(ViewForGrid.class, "select * from view_for_grid where view_name = '" + view_name + "';"));
            if (ret.size() == 0) {
                resp.send(403, view_name + " not existed ");
            }
            resp.render(Render.json(ret.get(0)));
        });


        get("/:name/query_data", (req, resp) -> {
            String view_name = req.paramNonBlank("name", "名称不能为空");
            String query_data = req.param("query_data");
            SqlSelect g_sql = Sql.select("*").from(view_name);

            List<ViewForGrid> listForView =  dataBaseService.getDB().get(t -> t.query(ViewForGrid.class, "select * from view_for_grid where vid = '" + view_name + "';"));
            if (listForView.size() == 0) {
                resp.send(403, view_name + " not existed ");
            }

            if (STRING.notBlank(query_data)) {
                List<Map<String, Object>> list = (List<Map<String, Object>>) JSON.parse(query_data);
                for (Map<String, Object> item : list) {
                    String item_key = (String) item.get("key");
                    Map<String, String> subItem = (Map<String, String>) item.get("value");
                    if ("in".equals(subItem.get("key"))) {
                        g_sql.where(item_key, Criterion.EQ, subItem.get("value"));
                    }
                    if ("equal".equals(subItem.get("key"))) {
                        g_sql.where(item_key, Criterion.EQ, subItem.get("value"));
                    }
                }
            }
            List<Record> lst =  dataBaseService.getDB().get(t -> t.query(g_sql));
            List lstShowCols = JSON.parseArray(listForView.get(0).get("show_columns"));
            List<Map<String, String>> lstRet = new ArrayList<>();
            Date date = new Date();//如果不需要格式,可直接用dt,dt就是当前系统时间
            DateFormat dateFrom = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");//设置显示格式

            S._for(lst).each(item -> {
                Map<String, String> map = new LinkedHashMap<String, String>();
                for (Object subItem : lstShowCols) {
                    if (subItem.toString().indexOf("_date") >= 0 || subItem.toString().indexOf("_time") >= 0) {
                        if (null == item.get(subItem.toString()) || 0 == (Long) item.get(subItem.toString())) {
                            map.put(subItem.toString(), "");
                        } else {
                            long dateNo = item.get(subItem.toString());
                            map.put(subItem.toString(), dateFrom.format(new Date(dateNo)));
                        }

                    } else {
                        map.put(subItem.toString(), item.get(subItem.toString()));
                    }

                }
                lstRet.add(map);
            });

            resp.render(Render.json(lstRet));
        });
        post("/new", (req, resp) -> {
            String json = req.param("data");
            String expressjson = req.param("expressData");

            List<ViewForGrid> ret =  dataBaseService.getDB().get(t -> t.query(ViewForGrid.class, "select * from view_for_grid where view_name = '" + dataBaseService.getValueByKeyFromJson("view_name", json) + "';"));
            if (ret.size() >= 1) {
                resp.send(403, "view exists");
                return;
            }
            String sql = dataBaseService.getCreateViewSql(json);
            if (STRING.isBlank(sql)) {
                resp.send(403, "sql create failed");
                return;
            }
            dataBaseService.getDB().post(t -> t.exec(sql));
            ViewForGrid vfg = new ViewForGrid();
            ViewForGrid vfgTemplate = null;
            String vid = dataBaseService.getValueByKeyFromJson("vid", json);
            String templateName = dataBaseService.getValueByKeyFromJson("template_name", json);

            vfg.setId(vid);
            vfg.set("view_name", dataBaseService.getValueByKeyFromJson("view_name", json));
            vfg.set("view_sql", sql);
            vfg.set("grid_options", dataBaseService.getValueByKeyFromJson("grid_options", json));
            vfg.set("template_name", templateName);

            if (templateName.startsWith(ConstDataBase.DATA_TABLE_HEAD_FLAG)) {
                vfg.set("main_table", templateName);
            } else {
                vfgTemplate = dataBaseService.getViewForGridByVid(templateName);
                vfg.set("main_table", vfgTemplate.get("main_table"));
            }

            vfg.set("query_html", dataBaseService.getValueByKeyFromJson("query_html", json));
            vfg.set("query_ids", dataBaseService.getValueByKeyFromJson("new_query_ids", json));
            vfg.set("show_columns", dataBaseService.getValueByKeyFromJson("show_columns", json));
            vfg.set("template_columns", dataBaseService.getValueByKeyFromJson("template_columns", json));
            vfg.set("cols_of_sub_groups", dataBaseService.getValueByKeyFromJson("cols_of_sub_groups", json));
            vfg.set("is_single_group", dataBaseService.getValueByKeyFromJson("is_single_group", json));
            List listShowTables = JSON.parseArray(dataBaseService.getValueByKeyFromJson("show_tables", json));
            List<String> listRelationalDataTables = new ArrayList<String>();
            List<String> listSelectCols = new ArrayList<String>();
            for (int i = 0; i < listShowTables.size(); i++) {
                String item = listShowTables.get(i).toString();
                if (item.startsWith(ConstDataBase.DATA_TABLE_HEAD_FLAG) && (listRelationalDataTables.indexOf(item) < 0)) {
                    listRelationalDataTables.add(item);
                    List<String> listColsOfDataTable = dataBaseService.getColumnsListOfDataTable(item);
                    for (String subItem : listColsOfDataTable) {
                        listSelectCols.add(subItem + ConstDataBase.V_AND + item + ConstDataBase.V_AND + vid);
                    }
                } else if (item.startsWith(ConstDataBase.VIEW_HRAD_FLAG)) {
                    ViewForGrid subVfg = dataBaseService.getViewForGridByVid(item);
                    if (null != vfg) {
                        if (STRING.notBlank(subVfg.get("relational_data_tables").toString())) {
                            List subViewTables = JSON.parseArray(subVfg.get("relational_data_tables").toString());
                            listRelationalDataTables.addAll(subViewTables);
                        }
                        if (STRING.notBlank(subVfg.get("select_columns").toString())) {
                            List subViewCols = JSON.parseArray(subVfg.get("select_columns").toString());
                            //其他视图需要补充替换vid，才能下次调用可以查询该列名 --
                            for (int idx = 0; idx < subViewCols.size(); idx++) {
                                String[] selectColsArr = subViewCols.get(idx).toString().split(ConstDataBase.V_AND);
                                if (selectColsArr.length >= 4) {
                                    subViewCols.set(idx, selectColsArr[0] + ConstDataBase.V_AND
                                            + selectColsArr[1] + ConstDataBase.V_AND
                                            + selectColsArr[2] + ConstDataBase.V_AND
                                            + vid);
                                } else {
                                    subViewCols.set(idx, subViewCols.get(idx).toString() + ConstDataBase.V_AND + vid);
                                }
                            }

                            listSelectCols.addAll(subViewCols);
                        }

                    } else {
                        resp.send(403, "err parm");
                    }
                } else {
                    resp.send(403, "err parm");
                    return;
                }
            }

            /* 考虑不是单组的模版，需要删除vid，添加他的子组 */
            List listForShowTables = JSON.parseArray(dataBaseService.getValueByKeyFromJson("show_tables", json));
            if (null != vfgTemplate && vfgTemplate.get("is_single_group").equals("no")) {
                listForShowTables.remove(templateName);
                listForShowTables.addAll(JSON.parseArray(vfgTemplate.get("show_tables")));
            }

            vfg.set("show_tables", JSON.toJSONString(listForShowTables));
            vfg.set("select_columns", JSON.toJSONString(listSelectCols));//
            vfg.set("relational_data_tables", JSON.toJSONString(listRelationalDataTables));//
            vfg.set("auto_id", Integer.parseInt(vid.substring(1, vid.length())));

             dataBaseService.getDB().post(t -> {
                t.recordInsert(vfg);
            });

            if (STRING.notBlank(expressjson)) {
                List expressList = JSON.parseArray(expressjson);
                for (Object item : expressList) {
                    ViewForExpress vfe = new ViewForExpress();
                    vfe.set("vid", ((Map<String, Object>) item).get("vid"));
                    vfe.set("header_name", ((Map<String, Object>) item).get("header_name"));
                    vfe.set("expressions", ((Map<String, Object>) item).get("expressions"));
                    vfe.set("group_name", ((Map<String, Object>) item).get("group_name"));
                    vfe.set("members", ((Map<String, Object>) item).get("members"));
                    vfe.set("is_has_exp_group", ((Map<String, Object>) item).get("is_has_exp_group"));
                     dataBaseService.getDB().post(t -> {
                        t.recordInsert(vfe);
                    });
                }
            }
            resp.send(200, "ok");
        });
        del("/:id", (req, resp) -> {
            String vid = req.param("id");
            if (STRING.isBlank(vid)) {
                resp.send("null parm");
            }
            ViewForGrid vfg = new ViewForGrid();
            vfg.setId(vid);
             dataBaseService.getDB().post(t -> {
                t.recordDelete(vfg);
            });
            resp.send(200, "ok");
        });

    }
}
